/*
 * Cambios en las funciones que generan los libros para afip. 
 * Cuando es NO CORRESPONDE debe dejar la posicion en blanco (antes ponia un cero)
 *
 * script general. Incidente reportado por Mariano (unitech)
 *
 * Ale - 23/12/2020
 */


-- Function: afip.rg3685_ventas()

CREATE OR REPLACE FUNCTION afip.rg3685_ventas()
  RETURNS character varying AS
$BODY$
DECLARE
BEGIN

CREATE TABLE afip.rg3685_ventas
(
	tipo_registro character varying(10),
	nro_linea integer, 
	repeticiones integer, 
	idcomm integer,
	fecha date,
	tipocomprobante integer,
	letra character varying(1),
	nroptovta integer,
	nro integer,
	nrofinal integer,
	coddocumento integer,
	cuit numeric(11,0),
	nombre character varying(50),
	codigo_operacion character varying(1),
	cant_alicuotas integer,
	alicuota numeric(4,0),
	impo_netogravado numeric(15,0),
	impo_nogravado numeric(15,0),
	impo_exento numeric(15,0),
	impo_iva numeric(15,0),
	impo_perc_nocategorizados numeric(15,0),
	impo_perc_nacionales numeric(15,0),
	impo_perc_provinciales numeric(15,0),
	impo_perc_municipales numeric(15,0),
	impo_imp_internos numeric(15,0),
	impo_total numeric(15,0), 
	impo_base_para_iva numeric(15,0)
)
WITH ( OIDS=TRUE );
ALTER TABLE afip.rg3685_ventas OWNER TO abacouser;
-- Tabla de ivas por comprobante.
CREATE TABLE afip.rg3685_ventas_iva
(
	idcomm integer,
	impo_base_para_iva numeric(15,0), 
	alicuota numeric(4,0),
	impo_iva numeric(15,0)
)
WITH ( OIDS=TRUE );
ALTER TABLE afip.rg3685_ventas_iva OWNER TO abacouser;

-- INSERTAMOS EN LA TABLA rg3685_ventas LO GENERADO POR EL PROCESO DE EXPORTACION INF. ELECTRONICA CON TIPO_REGISTRO '1-Iva'

INSERT INTO afip.rg3685_ventas(tipo_registro, idcomm, fecha, tipocomprobante, nroptovta, nro, nrofinal, coddocumento, cuit, nombre, codigo_operacion, alicuota, impo_netogravado, 
			impo_nogravado, impo_exento, impo_iva, impo_perc_nocategorizados, impo_perc_nacionales, impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, impo_total)
SELECT '1-Iva' as tipo_registro, cast(substring(linea, 1,10) as integer) as idcomm, cast(substring(linea, 11,8) as date) as fecha, cast(substring(linea, 19,3) as integer)  as tipocomprobante, 
	cast(substring(linea, 22,5) as integer)  as  nroptovta, cast(substring(linea, 27,15) as integer)  as  nro, 0 as nrofinal, 
	0 as coddocumento, cast(substring(linea, 42,11) as numeric(11,0)) as cuit, substring(linea, 53,30) as nombre, '0' as codigo_operacion,
	cast(substring(linea, 83,4) as numeric(4,0)) as alicuota, 
	cast(substring(linea, 87,15) as numeric(15,0)) as impo_netogravado, 
	cast(substring(linea, 102,15) as numeric(15,0)) as impo_nogravado,
	cast(substring(linea, 117,15) as numeric(15,0)) as impo_exento, 
	cast(substring(linea, 132,15) as numeric(15,0)) as impo_iva,
	0 as impo_perc_nocategorizados, 
	cast(substring(linea, 147,15) as numeric(15,0)) as impo_perc_nacionales, 
	cast(substring(linea, 162,15) as numeric(15,0)) as impo_perc_provinciales, 
	cast(substring(linea, 177,15) as numeric(15,0)) as impo_perc_municipales, 
	cast(substring(linea, 192,15) as numeric(15,0)) as impo_imp_internos, 
	cast(substring(linea, 207,15) as numeric(15,0)) as impo_total
FROM tmp_afiplib
ORDER BY oid;

-- INVERTIMOS LOS SIGNOS DE LOS IMPORTES EN FUNCION DE LOS SIGNOS POR COMPROBANTE DE LA CONFIGURACION DEL LIBRO FISCAL.        CUIDADO QUE USA EL AFIPLIB.ID=101
-- ASI VOLVEMOS A DAR VUELTA LO QUE PREVIAMENTE DIO VUELTA EL LIBRO FISCAL QUE PUSO LOS IMPORTES DE LAS NOTAS DE CREDITO EN NEGATIVO.
-- ASI QUEDARAN EN NEGATIVO UNICAMENTE LOS IMPORTES NEGATIVOS CORRESPONDIENTES A CADA TIPO DE COMPROBANTE PARA LA AFIP.
UPDATE afip.rg3685_ventas ventas 
	SET impo_netogravado = impo_netogravado * ctblibcom.signo, 
		impo_nogravado = impo_nogravado * ctblibcom.signo, 
		impo_exento = impo_exento * ctblibcom.signo, 
		impo_iva = impo_iva * ctblibcom.signo, 
		impo_perc_nocategorizados = impo_perc_nocategorizados * ctblibcom.signo, 
		impo_perc_nacionales = impo_perc_nacionales * ctblibcom.signo, 
		impo_perc_provinciales = impo_perc_provinciales * ctblibcom.signo, 
		impo_perc_municipales = impo_perc_municipales * ctblibcom.signo, 
		impo_imp_internos = impo_imp_internos * ctblibcom.signo, 
		impo_total = impo_total * ctblibcom.signo, 
		impo_base_para_iva = impo_base_para_iva * ctblibcom.signo
FROM comm, afiplib, ctblibcom
WHERE ventas.idcomm = comm.id and afiplib.id = 101 and afiplib.codctblib = ctblibcom.codctblib and comm.tipocom = ctblibcom.tipocom and ctblibcom.signo is not null;

-- Agregamos algunos datos del comprobante.

-- Letra
UPDATE afip.rg3685_ventas ventas
SET letra = comm.letra, nrofinal = coalesce(comm.nrofinal, comm.nro)
FROM comm
WHERE ventas.idcomm = comm.id;

-- coddocumento, lo tomamos de la tabla paddoc. Primero por pad.tipocuit y despues por pad.tipopaddoc.
UPDATE afip.rg3685_ventas ventas 
SET coddocumento = paddoc.coddgi
FROM comm, pad, paddoc 
WHERE ventas.coddocumento = 0 and ventas.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id and pad.tipocuit = paddoc.tipo;

-- Definimos tipo de documento 099(Sin identificar/Venta Global Diaria)  para las gran B. Otros ej. de tipo documento son: 80 CUIT, 81 CUIL
UPDATE afip.rg3685_ventas ventas
SET coddocumento = 99
FROM comm
WHERE ventas.idcomm = comm.id and comm.tipocom = 'GB';

-- Seteo el cuit del padfiscal si cuit es cero. Se da en los casos en que raramente comm.idpadfiscal es nulo.
UPDATE afip.rg3685_ventas ventas
SET cuit = cast(str_a_nro(replace(pad.cuit, '-', '')) as numeric(11,0))
FROM comm, pad
WHERE ventas.cuit = 0 and ventas.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id;

-- Seteo el nombre del padfiscal si nombre es nulo. Se da en los casos en que raramente comm.idpadfiscal es nulo.
UPDATE afip.rg3685_ventas ventas
SET nombre = pad.razonsocial
FROM comm, pad
WHERE coalesce(trim(ventas.nombre), '') = '' and ventas.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id;

-- Utilizamos los datos personales del cliente (DNI) si no tiene datos fiscales (CUIT).
UPDATE afip.rg3685_ventas ventas
	SET coddocumento = paddoc.coddgi,
	    cuit = cast(str_a_nro(replace(pad.nrodoc, '-', '')) as numeric(11, 0))
FROM comm, pad, paddoc
WHERE ventas.coddocumento = 0 and comm.tipocom <> 'GB' and ventas.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id and pad.tipopaddoc = paddoc.tipo and
      cast(str_a_nro(replace(coalesce(pad.nrodoc, ''), '-', '')) as numeric(11, 0)) <> 0;

-- Cuando es una Venta a un cliente con Cond.IVA = Consumidor Final y
-- el cliente no tiene DNI se informa como Codigo de Documento = 96 (DNI)
-- y como Numero de Documento = 1 para que el aplicativo no informe errores.

UPDATE afip.rg3685_ventas ventas 
SET coddocumento = 96, cuit = 1
FROM comm, pad
WHERE ventas.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id and pad.codpadiva = 'F' and pad.nrodoc is null and ventas.cuit = 0 and comm.tipocom <> 'GB';

-- Ponemos en cero nroptovta cuando se tratan de liquidaciones de cereales.
UPDATE afip.rg3685_ventas SET nroptovta = 0 WHERE tipocomprobante in (33, 332);

-- Eliminamos los comprobantes que tienen toooodas las columnas de importe en cero.
-- Caso comprobantes anulados, y los cobros de tarjetas donde, segun la configuracion del libro fiscal, a veces el importe del cobro se resta de la columna del total y todo queda en cero.
DELETE FROM afip.rg3685_ventas
WHERE impo_netogravado = 0 and impo_nogravado = 0 and impo_exento = 0 and impo_iva = 0 and
	impo_perc_nocategorizados = 0 and
	impo_perc_nacionales = 0 and impo_perc_provinciales = 0 and 
	impo_perc_municipales = 0 and impo_imp_internos = 0 and impo_total = 0;
	
-- Agregamos filas con alicuota en cero para los que tienen algo en iva, pero tambien en exento o no gravado.
INSERT INTO afip.rg3685_ventas(tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, nrofinal,
		coddocumento, cuit, nombre, codigo_operacion, alicuota, impo_netogravado, 
		impo_nogravado, impo_exento, impo_iva, impo_perc_nocategorizados, impo_perc_nacionales, 
		impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, impo_total)
SELECT tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, nrofinal,
	coddocumento, cuit, nombre, codigo_operacion, 0 as alicuota, 0 as impo_netogravado, 
	impo_nogravado, impo_exento, 0 as impo_iva, 0 as impo_perc_nocategorizados, 0 as impo_perc_nacionales, 
	0 as impo_perc_provinciales, 0 as impo_perc_municipales, 0 as impo_imp_internos, 0 as impo_total
FROM afip.rg3685_ventas
WHERE impo_iva <> 0 and (impo_nogravado <> 0 or impo_exento <> 0);

-- Cereo el exento y nogravado de las filas que tome como base para generar las nuevas filas con piva en cero.
-- Uso el MISMO where que el anterior.
UPDATE afip.rg3685_ventas ventas
SET impo_nogravado = 0, impo_exento = 0 
WHERE impo_iva <> 0 and (impo_nogravado <> 0 or impo_exento <> 0);

-- Seteamos impo_base_para_iva. CONSIDERAMOS AHORA EL IVA CERO PARA ALGUNOS PRODUCTO MACRI.
UPDATE afip.rg3685_ventas ventas
SET impo_base_para_iva = case when impo_iva = 0 and alicuota <> 1 then impo_nogravado + impo_exento else impo_netogravado end;

-- EJECUTAMOS PROCESOS A MEDIDA DEL CLIENTE
PERFORM afip.rg3685_ventas_especificos();

-- INSERTAMOS EN EL FINAL DE LA TABLA rg3685_ventas UNA FILA POR COMPROBANTE, AGRUPANDO POR IDCOMM CON TIPO_REGISTRO '2-Comp'
-- No consideramos el campo impo_base_para_iva porque para los comprobantes amuchados no se necesita.
INSERT INTO afip.rg3685_ventas(tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, nrofinal, 
	coddocumento, cuit, nombre, codigo_operacion, alicuota, impo_netogravado, 
	impo_nogravado, impo_exento, impo_iva, impo_perc_nocategorizados, impo_perc_nacionales, 
	impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, impo_total)
SELECT '2-Comp' as tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, nrofinal,
	coddocumento, cuit, nombre, max(codigo_operacion) as codigo_operacion,  
	null as alicuota, sum(impo_netogravado) as impo_netogravado, 
	sum(impo_nogravado) as impo_nogravado, sum(impo_exento) as impo_exento, sum(impo_iva) as impo_iva, 
	sum(impo_perc_nocategorizados) as impo_perc_nocategorizados, sum(impo_perc_nacionales) as impo_perc_nacionales, 
	sum(impo_perc_provinciales) as impo_perc_provinciales, sum(impo_perc_municipales) as impo_perc_municipales, 
	sum(impo_imp_internos) as impo_imp_internos, sum(impo_total) as impo_total
FROM afip.rg3685_ventas
GROUP BY idcomm, fecha, tipocomprobante, letra, nroptovta, nro, nrofinal, coddocumento, cuit, nombre, codigo_operacion
ORDER BY max(oid);

-- Numeramos las filas del tipo de registro 2.
DROP SEQUENCE IF EXISTS afip.seq_aux;

CREATE SEQUENCE afip.seq_aux INCREMENT 1 MINVALUE 1 START 1;
ALTER TABLE afip.seq_aux OWNER TO abacouser;

UPDATE afip.rg3685_ventas SET nro_linea=x.nro_linea
FROM (SELECT oid, NEXTVAL('afip.seq_aux') AS nro_linea FROM afip.rg3685_ventas WHERE tipo_registro = '2-Comp' ORDER BY idcomm, oid) x
WHERE afip.rg3685_ventas.oid = x.oid;

-- ================================================
-- PROCESO EXPORTACION POR ALICUOTAS DE IVA VENTAS
-- ================================================
-- Llenamos la tabla auxiliar de importes por alicuota.
INSERT INTO afip.rg3685_ventas_iva (idcomm, impo_base_para_iva, alicuota, impo_iva)
SELECT idcomm, sum(impo_base_para_iva) as impo_base_para_iva, alicuota, sum(impo_iva) as impo_iva
FROM afip.rg3685_ventas ventas
WHERE tipo_registro = '1-Iva'
GROUP BY 1,3
ORDER BY ventas.idcomm;

-- Eliminamos las filas con alicuota pero sin importe de iva. EXEPTO LAS ALICUOTAS 0.01 QUE PERTENCEN A ARTICULOS TEMPORALMENTE CON IVA CERO. Agosto 2019. (MACRI)
-- Nota: El campo alicuota esta multiplicado por 100.
DELETE FROM afip.rg3685_ventas_iva WHERE alicuota > 1 and impo_iva = 0;

-- Eliminamos las filas de iva que tienen todo en cero, a menos que sea la unica.
delete from afip.rg3685_ventas_iva
where impo_base_para_iva = 0 and alicuota = 0 and impo_iva = 0 and 
	    coalesce((select count(*) from afip.rg3685_ventas_iva filas_iva where afip.rg3685_ventas_iva.idcomm = filas_iva.idcomm), 0) > 1;

-- Actualizamos la cantidad de alicuotas del master en funcion de la tabla recien generada.
UPDATE afip.rg3685_ventas ventas
SET cant_alicuotas = coalesce((select count(*) from afip.rg3685_ventas_iva x where ventas.idcomm = x.idcomm), 0)
WHERE ventas.tipo_registro = '2-Comp';

-- Ponemos una E en codigo_operacion si no hubo iva en el comprobante. Para cualquier letra.
UPDATE afip.rg3685_ventas ventas
SET codigo_operacion = 'E'
WHERE ventas.tipo_registro = '2-Comp' and
exists (select x.alicuota from afip.rg3685_ventas_iva x where ventas.idcomm = x.idcomm and x.alicuota = 0 and x.impo_base_para_iva <> 0);

-- Ponemos tambien una E cuando no hay ningun importe excepto algun impuesto, parece que para este caso la afip tambien quiere una E aunque se trate de impuestos.
-- En Petro hubo una nota de debito proveedor con SOLO impuesto internos y el aplicativo quiere una E para este caso.
UPDATE afip.rg3685_ventas ventas
SET codigo_operacion = 'E'
WHERE ventas.tipo_registro = '2-Comp' AND impo_netogravado = 0 AND
	( impo_nogravado <> 0 or impo_exento <> 0 or impo_perc_nacionales <> 0 or impo_perc_provinciales <> 0 or impo_perc_municipales <> 0 or impo_imp_internos <> 0);

-- si quedo en null o '0' (cero) va espacios
UPDATE afip.rg3685_ventas ventas
SET codigo_operacion = ' '
WHERE codigo_operacion is null or codigo_operacion = '0';

-- Ponemos en cero el neto gravado de las alicuotas en cero.
UPDATE afip.rg3685_ventas_iva SET impo_base_para_iva = 0 WHERE alicuota = 0;

-- Seteamos repeticiones para un mismo comprobante, un cero indica sin repeticion, un uno significa que esta repetida una vez.
UPDATE afip.rg3685_ventas ventas
SET repeticiones = coalesce((select count(*) from afip.rg3685_ventas x where ventas.idcomm = x.idcomm and x.tipo_registro = '2-Comp'), 0)
WHERE ventas.tipo_registro = '2-Comp';
UPDATE afip.rg3685_ventas ventas
SET repeticiones = repeticiones - 1
WHERE ventas.tipo_registro = '2-Comp';


-- ==================================================
-- ULTIMO PASO: GENERAMOS LAS TABLAS FINALES DE TEXTO
-- ==================================================
-- GENERAMOS LA TABLA afip.tmp_rg3685_ventas PARA COMPROBANTES.

CREATE TABLE afip.tmp_rg3685_ventas
( linea character varying(1000) NOT NULL -- Linea a exportar
)WITH ( OIDS=TRUE );

INSERT INTO afip.tmp_rg3685_ventas(linea)
SELECT to_char(fecha, 'YYYYMMDD') ||
	trim(to_char(tipocomprobante, '000')) ||
	trim(to_char(nroptovta, '00000')) ||
	trim(to_char(nro, '00000000000000000000')) ||
	trim(to_char(coalesce(nrofinal,0), '00000000000000000000')) ||
	case when coddocumento = 0 then '??' else trim(to_char(coddocumento, '00')) end ||
	trim(to_char(cuit, '00000000000000000000')) ||
	rpad(nombre, 30, ' ') ||
	str_nro_to_char(impo_total, '000000000000000') ||
	str_nro_to_char(impo_nogravado, '000000000000000') ||
	str_nro_to_char(impo_perc_nocategorizados, '000000000000000') ||
	str_nro_to_char(impo_exento, '000000000000000') ||
	str_nro_to_char(impo_perc_nacionales, '000000000000000') ||
	str_nro_to_char(impo_perc_provinciales, '000000000000000') ||
	str_nro_to_char(impo_perc_municipales, '000000000000000') ||
	str_nro_to_char(impo_imp_internos, '000000000000000') ||
	'PES' || '0001000000' ||
	trim(to_char(cant_alicuotas, '0')) ||
	codigo_operacion ||
	'000000000000000' ||   -- Otros tributos.
	'00000000'    -- Fecha de vencimiento de pago.
FROM afip.rg3685_ventas
WHERE tipo_registro = '2-Comp'
ORDER BY nro_linea;


-- ================================================
-- PROCESO EXPORTACION POR ALICUOTAS DE IVA VENTAS
-- ================================================
-- REGENERAMOS LA TABLA afip.tmp_rg3685_alicuotas_ventas PARA ALICUOTAS VENTAS.

CREATE TABLE afip.tmp_rg3685_alicuotas_ventas
( linea character varying(1000) NOT NULL -- Linea a exportar
)WITH ( OIDS=TRUE );

INSERT INTO afip.tmp_rg3685_alicuotas_ventas(linea)
SELECT trim(to_char(ventas.tipocomprobante, '000')) ||
	trim(to_char(ventas.nroptovta, '00000')) ||
	trim(to_char(ventas.nro, '00000000000000000000')) ||
	str_nro_to_char(ivas.impo_base_para_iva, '000000000000000') ||
	coalesce(trim(to_char(comiva.afipcod, '0000')), '????') ||
	str_nro_to_char(ivas.impo_iva, '000000000000000')
FROM afip.rg3685_ventas_iva ivas
JOIN afip.rg3685_ventas ventas ON ivas.idcomm = ventas.idcomm and ventas.tipo_registro = '2-Comp'
LEFT JOIN comiva ON cast(comiva.tasa * 100 as integer) = cast(ivas.alicuota as integer)
ORDER BY ventas.nro_linea, ivas.oid;

RETURN '';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION afip.rg3685_ventas() OWNER TO abacouser;

-- =============
-- Function: afip.rg3685_compras()

-- DROP FUNCTION afip.rg3685_compras();

CREATE OR REPLACE FUNCTION afip.rg3685_compras()
  RETURNS character varying AS
$BODY$
DECLARE
BEGIN
CREATE TABLE afip.rg3685_compras
(
	tipo_registro character varying(10),
	nro_linea integer, 
	repeticiones integer, 
	idcomm integer,
	fecha date,
	tipocomprobante integer,
	letra character varying(1),
	nroptovta integer,
	nro integer,
	coddocumento integer,
	cuit numeric(11,0),
	nombre character varying(50),
	codigo_operacion character varying(1),
	cuit_emisor_corredor numeric(11,0), 
	nombre_emisor_corredor character varying(30),
	cant_alicuotas integer,
	alicuota numeric(4,0),
	impo_netogravado numeric(15,0), 
	impo_nogravado numeric(15,0),
	impo_exento numeric(15,0),
	impo_iva numeric(15,0),
	impo_perc_iva numeric(15,0),
	impo_perc_nacionales numeric(15,0),
	impo_perc_provinciales numeric(15,0),
	impo_perc_municipales numeric(15,0),
	impo_imp_internos numeric(15,0),
	impo_total numeric(15,0), 
	impo_base_para_iva numeric(15,0),
	detalle character varying(50)
)
WITH ( OIDS=TRUE );
ALTER TABLE afip.rg3685_compras OWNER TO abacouser;
-- Tabla de ivas por comprobante.
CREATE TABLE afip.rg3685_compras_iva
(
	idcomm integer,
	impo_base_para_iva numeric(15,0), 
	alicuota numeric(4,0),
	impo_iva numeric(15,0)
)
WITH ( OIDS=TRUE );
ALTER TABLE afip.rg3685_compras_iva OWNER TO abacouser;
-- INSERTAMOS EN LA TABLA rg3685_compras LO GENERADO POR EL PROCESO DE EXPORTACION INF. ELECTRONICA CON TIPO_REGISTRO '1-Iva'
INSERT INTO afip.rg3685_compras(
	tipo_registro, idcomm, fecha, tipocomprobante, nroptovta, nro, 
	coddocumento, cuit, nombre, codigo_operacion, alicuota, impo_netogravado, 
	impo_nogravado, impo_exento, impo_iva, impo_perc_iva, impo_perc_nacionales, 
	impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, impo_total, detalle)
	SELECT '1-Iva' as tipo_registro, cast(substring(linea, 1,10) as integer) as idcomm, cast(substring(linea, 11,8) as date) as fecha, 
	cast(substring(linea, 19,3) as integer)  as tipocomprobante, 
	cast(substring(linea, 22,5) as integer)  as  nroptovta, cast(substring(linea, 27,15) as integer)  as  nro, 
	0 as coddocumento, cast(substring(linea, 42,11) as numeric(11,0)) as cuit, substring(linea, 53,30) as nombre,
	'0' as codigo_operacion,
	cast(substring(linea, 83,4) as numeric(4,0)) as alicuota, 
	cast(substring(linea, 87,15) as numeric(15,0)) as impo_netogravado, 
	cast(substring(linea, 102,15) as numeric(15,0)) as impo_nogravado,
	cast(substring(linea, 117,15) as numeric(15,0)) as impo_exento, 
	cast(substring(linea, 132,15) as numeric(15,0)) as impo_iva, 
	cast(substring(linea, 147,15) as numeric(15,0)) as impo_perc_iva, 
	cast(substring(linea, 162,15) as numeric(15,0)) as impo_perc_nacionales, 
	cast(substring(linea, 177,15) as numeric(15,0)) as impo_perc_provinciales, 
	cast(substring(linea, 192,15) as numeric(15,0)) as impo_perc_municipales, 
	cast(substring(linea, 207,15) as numeric(15,0)) as impo_imp_internos, 
	cast(substring(linea, 222,15) as numeric(15,0)) as impo_total,
	substring(linea, 237,16) as detalle
FROM tmp_afiplib
ORDER BY oid;
-- INVERTIMOS LOS SIGNOS DE LOS IMPORTES EN FUNCION DE LOS SIGNOS POR COMPROBANTE DE LA CONFIGURACION DEL LIBRO FISCAL.        CUIDADO QUE USA EL AFIPLIB.ID=100
-- ASI VOLVEMOS A DAR VUELTA LO QUE PREVIAMENTE DIO VUELTA EL LIBRO FISCAL QUE PUSO LOS IMPORTES DE LAS NOTAS DE CREDITO EN NEGATIVO.
-- ASI QUEDARAN EN NEGATIVO UNICAMENTE LOS IMPORTES NEGATIVOS CORRESPONDIENTES A CADA TIPO DE COMPROBANTE PARA LA AFIP.
UPDATE afip.rg3685_compras compras 
    SET impo_netogravado = impo_netogravado * ctblibcom.signo, 
	impo_nogravado = impo_nogravado * ctblibcom.signo, 
	impo_exento = impo_exento * ctblibcom.signo, 
	impo_iva = impo_iva * ctblibcom.signo, 
	impo_perc_iva = impo_perc_iva * ctblibcom.signo, 
	impo_perc_nacionales = impo_perc_nacionales * ctblibcom.signo, 
	impo_perc_provinciales = impo_perc_provinciales * ctblibcom.signo, 
	impo_perc_municipales = impo_perc_municipales * ctblibcom.signo, 
	impo_imp_internos = impo_imp_internos * ctblibcom.signo, 
	impo_total = impo_total * ctblibcom.signo, 
	impo_base_para_iva = impo_base_para_iva * ctblibcom.signo
FROM comm, afiplib, ctblibcom
WHERE compras.idcomm = comm.id and afiplib.id = 100 and afiplib.codctblib = ctblibcom.codctblib and comm.tipocom = ctblibcom.tipocom and ctblibcom.signo is not null;

-- Agregamos algunos datos del comprobante.
-- coddocumento, lo tomamos de la tabla paddoc. Primero por pad.tipocuit y despues por pad.tipopaddoc.
UPDATE afip.rg3685_compras compras 
SET coddocumento = paddoc.coddgi
FROM comm, pad, paddoc 
WHERE compras.coddocumento = 0 and compras.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id and pad.tipocuit = paddoc.tipo;

UPDATE afip.rg3685_compras compras 
SET coddocumento = paddoc.coddgi
FROM comm, pad, paddoc 
WHERE compras.coddocumento = 0 and compras.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id and pad.tipopaddoc = paddoc.tipo;

-- Seteo el cuit del padfiscal si cuit es cero. Se da en los casos en que raramente comm.idpadfiscal es nulo.
UPDATE afip.rg3685_compras compras 
SET cuit = cast(str_a_nro(replace(pad.cuit, '-', '')) as numeric(11,0))
FROM comm, pad
WHERE compras.cuit = 0 and compras.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id;

-- Seteo el nombre del padfiscal si nombre es nulo. Se da en los casos en que raramente comm.idpadfiscal es nulo.
UPDATE afip.rg3685_compras compras 
SET nombre = pad.razonsocial
FROM comm, pad
WHERE coalesce(trim(compras.nombre), '') = '' and compras.idcomm = comm.id and coalesce(comm.idpadfiscal, comm.idpad) = pad.id;

-- Le ponemos 80 (cuit) para los que no tienen tipo. Despues saltara por numero de cuit invalido.
UPDATE afip.rg3685_compras SET coddocumento = 80 WHERE coddocumento = 0;

-- Letra
UPDATE afip.rg3685_compras compras
SET letra = comm.letra
FROM comm
WHERE compras.idcomm = comm.id;

-- Cuit emisor/corredor para las operaciones de canje de cereales, liquidacion de fertilizantes, etc.
UPDATE afip.rg3685_compras compras
SET cuit_emisor_corredor = CASE WHEN tipocomprobante IN (33,58,59,60,63) THEN cast(str_a_nro(replace(ambemp.cuit, '-', '')) as numeric(11,0)) ELSE 0 END, 
nombre_emisor_corredor = CASE WHEN tipocomprobante IN (33,58,59,60,63) THEN substring(ambemp.nombre, 1, 30) ELSE '' END
FROM comm, ambempsuc, ambemp
WHERE compras.idcomm = comm.id and comm.codambsuc = ambempsuc.codambsuc and ambempsuc.codambemp = ambemp.cod;

-- Ponemos en nroptovta un uno cuando es cero. Caso liquidaciones de tarjetas. VER SI NO TIENEN PUNTO O TIENEN Y NO LO PONEN.
UPDATE afip.rg3685_compras SET nroptovta = 1 WHERE nroptovta = 0;

-- Ponemos en cero nroptovta cuando se tratan de liquidaciones de cereales.
UPDATE afip.rg3685_compras SET nroptovta = 0 WHERE tipocomprobante in (33, 332);

-- Si impo_iva es cero y se trata de una factura C, pisamos alicuota en cero.
--       Existen comprobantes de compra letra C que tienen ivas en cero pero alicuotas en 21.
--       Si impo_iva es cero y es un factura C eliminamos el exento y no gravado porque se informa solo el total.
-- Nota: Hacemos las mismas consideraciones para letra C y B, porque puede haber facturas B de gastos de hoteleria, estacionamiento y otros conceptos.
UPDATE afip.rg3685_compras 
SET alicuota = 0, impo_nogravado = 0, impo_exento = 0
WHERE impo_iva = 0 and letra in ('C', 'B');

-- Pongo alicuota en cero cuando la fila no tiene impo_iva.
-- Se da en los putos casos de liquidaciones de tarjeta que le ponen al gasto un porc.de iva 21 pero no cargan el concepto iva, con lo cual ... no tiene iva.
UPDATE afip.rg3685_compras compras SET alicuota = 0 WHERE impo_iva = 0;

-- Enviamos impo_netogravado a impo_exento, para los casos que el subdiario lo puso en neto_gravado porque estaba con una tasa de iva, 
-- y luego el update anterior le puso alicuota en cero. (Caso liquidaciones tarjetas).
UPDATE afip.rg3685_compras
SET impo_exento = impo_exento + impo_netogravado, impo_netogravado = 0
WHERE impo_netogravado <> 0 and alicuota = 0;

-- Eliminamos los comprobantes que tienen toooodas las columnas de importe en cero.
-- Caso comprobantes anulados, y los cobros de tarjetas donde, segun la configuracion del libro fiscal, a veces el importe del cobro se resta de la columna del total y todo queda en cero.
DELETE FROM afip.rg3685_compras 
WHERE impo_netogravado = 0 and impo_nogravado = 0 and impo_exento = 0 and impo_iva = 0 and
impo_perc_iva = 0 and impo_perc_nacionales = 0 and impo_perc_provinciales = 0 and 
impo_perc_municipales = 0 and impo_imp_internos = 0 and impo_total = 0;

-- Pasan a cero el puesto y numero de documento en los tipos de comprobante 66
UPDATE afip.rg3685_compras compras
SET nroptovta = 0, nro = 0
WHERE tipocomprobante = 66;

-- Agregamos filas con alicuota en cero para los que tienen algo en iva, pero tambien en exento o no gravado.
INSERT INTO afip.rg3685_compras(
	tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, 
	coddocumento, cuit, nombre, codigo_operacion, cuit_emisor_corredor, nombre_emisor_corredor, alicuota, impo_netogravado, 
	impo_nogravado, impo_exento, impo_iva, impo_perc_iva, impo_perc_nacionales, 
	impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, impo_total, detalle)
SELECT 
	tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, 
	coddocumento, cuit, nombre, codigo_operacion, cuit_emisor_corredor, nombre_emisor_corredor, 0 as alicuota, 0 as impo_netogravado, 
	impo_nogravado, impo_exento, 0 as impo_iva, 0 as impo_perc_iva, 0 as impo_perc_nacionales, 
	0 as impo_perc_provinciales, 0 as impo_perc_municipales, 0 as impo_imp_internos, 0 as impo_total, detalle
FROM afip.rg3685_compras
WHERE impo_iva <> 0 and (impo_nogravado <> 0 or impo_exento <> 0);

-- Cereo el exento y nogravado de las filas que tome como base para generar las nuevas filas con piva en cero.
-- Uso el MISMO where que el anterior.
UPDATE afip.rg3685_compras compras
SET impo_nogravado = 0, impo_exento = 0 
WHERE impo_iva <> 0 and (impo_nogravado <> 0 or impo_exento <> 0);

-- Seteamos impo_base_para_iva.
UPDATE afip.rg3685_compras compras
SET impo_base_para_iva = case when impo_iva = 0 then impo_nogravado + impo_exento else impo_netogravado end;

-- EJECUTAMOS PROCESOS A MEDIDA DEL CLIENTE
PERFORM afip.rg3685_compras_especificos();

-- INSERTAMOS EN EL FINAL DE LA TABLA rg3685_compras UNA FILA POR COMPROBANTE, AGRUPANDO POR IDCOMM CON TIPO_REGISTRO '2-Comp'
-- No consideramos el campo impo_base_para_iva porque para los comprobantes amuchados no se necesita.
INSERT INTO afip.rg3685_compras(
	tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro, 
	coddocumento, cuit, nombre, codigo_operacion, cuit_emisor_corredor, nombre_emisor_corredor, alicuota, impo_netogravado, 
	impo_nogravado, impo_exento, impo_iva, impo_perc_iva, impo_perc_nacionales, 
	impo_perc_provinciales, impo_perc_municipales, impo_imp_internos, 
	impo_total, detalle)
SELECT '2-Comp' as tipo_registro, idcomm, fecha, tipocomprobante, letra, nroptovta, nro,
	coddocumento, cuit, nombre, coalesce(max(codigo_operacion),'0') as codigo_operacion, cuit_emisor_corredor, nombre_emisor_corredor, 
	null as alicuota, sum(impo_netogravado) as impo_netogravado, 
	sum(impo_nogravado) as impo_nogravado, sum(impo_exento) as impo_exento, sum(impo_iva) as impo_iva, 
	sum(impo_perc_iva) as impo_perc_iva, sum(impo_perc_nacionales) as impo_perc_nacionales, 
	sum(impo_perc_provinciales) as impo_perc_provinciales, sum(impo_perc_municipales) as impo_perc_municipales, 
	sum(impo_imp_internos) as impo_imp_internos, sum(impo_total) as impo_total, detalle
	from afip.rg3685_compras
	group by idcomm, fecha, tipocomprobante, letra, nroptovta, nro, coddocumento, cuit, nombre, codigo_operacion, cuit_emisor_corredor, nombre_emisor_corredor, detalle
	order by max(oid);

-- Numeramos las filas del tipo de registro 2.
DROP SEQUENCE IF EXISTS afip.seq_aux;
CREATE SEQUENCE afip.seq_aux INCREMENT 1 MINVALUE 1 START 1;

ALTER TABLE afip.seq_aux OWNER TO abacouser;
UPDATE afip.rg3685_compras SET nro_linea=x.nro_linea
FROM 
(SELECT oid, NEXTVAL('afip.seq_aux') AS nro_linea FROM afip.rg3685_compras WHERE tipo_registro = '2-Comp' ORDER BY idcomm, oid) x
WHERE afip.rg3685_compras.oid = x.oid;

-- ================================================
-- PROCESO EXPORTACION POR ALICUOTAS DE IVA COMPRAS
-- ================================================
-- Llenamos la tabla auxiliar de importes por alicuota.
INSERT INTO afip.rg3685_compras_iva (idcomm, impo_base_para_iva, alicuota, impo_iva)
SELECT idcomm, sum(impo_base_para_iva) as impo_base_para_iva, alicuota, sum(impo_iva) as impo_iva
FROM afip.rg3685_compras compras
WHERE tipo_registro = '1-Iva' and
(compras.letra not in ('C', 'B')  or compras.tipocomprobante = 66)
-- and ( compras.impo_base_para_iva  <> 0 or compras.alicuota = 0 ) and ( compras.alicuota = 0 or (compras.alicuota <> 0 and compras.impo_iva <> 0) )
GROUP BY 1,3
ORDER BY compras.idcomm;

-- Eliminamos las filas con alicuota pero sin importe de iva.
DELETE FROM afip.rg3685_compras_iva
WHERE alicuota <> 0 and impo_iva = 0;

-- Ponemos una E en codigo_operacion si no hubo iva en el comprobante. Para cualquier letra.
UPDATE afip.rg3685_compras compras
SET codigo_operacion = 'E'
WHERE compras.tipo_registro = '2-Comp' and
exists (select x.alicuota from afip.rg3685_compras_iva x where compras.idcomm = x.idcomm and x.alicuota = 0 and x. impo_base_para_iva <> 0);

-- Ponemos tambien una E cuando no hay ningun importe excepto algun impuesto, parece que para este caso la afip tambien quiere una E aunque se trate de impuestos.
-- En Petro hubo una nota de debito proveedor con SOLO impuesto internos y el aplicativo quiere una E para este caso.
UPDATE afip.rg3685_compras compras
SET codigo_operacion = 'E'
WHERE compras.tipo_registro = '2-Comp' AND
impo_netogravado = 0 AND
( impo_nogravado <> 0 or impo_exento <> 0 or impo_perc_iva <> 0 or impo_perc_nacionales <> 0 or impo_perc_provinciales <> 0 or impo_perc_municipales <> 0 
or impo_imp_internos <> 0);

-- si quedo en null o '0' (cero) va espacios
UPDATE afip.rg3685_compras compras
SET codigo_operacion = ' '
WHERE codigo_operacion is null or codigo_operacion = '0';

-- Ponemos en cero el neto gravado de las alicuotas en cero.
UPDATE afip.rg3685_compras_iva SET impo_base_para_iva = 0 WHERE alicuota = 0;

-- Seteamos repeticiones para un mismo comprobante, un cero indica sin repeticion, un uno significa que esta repetida una vez.
UPDATE afip.rg3685_compras compras
SET repeticiones = coalesce((select count(*) from afip.rg3685_compras x where compras.idcomm = x.idcomm and x.tipo_registro = '2-Comp'), 0)
WHERE compras.tipo_registro = '2-Comp';

UPDATE afip.rg3685_compras compras
SET repeticiones = repeticiones - 1
WHERE compras.tipo_registro = '2-Comp';

-- Eliminamos las filas de iva con alicuota en cero, repetidas (no sabemos por que se generan).
delete from afip.rg3685_compras_iva iva
where exists (select * from afip.rg3685_compras_iva iva1
			where   iva.idcomm = iva1.idcomm and
				iva.impo_base_para_iva = iva1.impo_base_para_iva and
				iva.alicuota = iva1.alicuota and
				iva.impo_iva = iva1.impo_iva and
				iva.alicuota = 0 and iva.impo_base_para_iva = 0 and iva.impo_iva = 0 and
				iva.oid > iva1.oid);

-- Eliminamos las filas de iva que tienen todo en cero, a menos que sea la unica.
delete from afip.rg3685_compras_iva
where impo_base_para_iva = 0 and alicuota = 0 and impo_iva = 0 and
coalesce((select count(*) from afip.rg3685_compras_iva filas_iva where afip.rg3685_compras_iva.idcomm = filas_iva.idcomm), 0) > 1;

-- Actualizamos la cantidad de alicuotas del master en funcion de la tabla recien generada.
UPDATE afip.rg3685_compras compras
SET cant_alicuotas = coalesce((select count(*) from afip.rg3685_compras_iva x where compras.idcomm = x.idcomm), 0)
WHERE compras.tipo_registro = '2-Comp';

-- ==================================================
-- ULTIMO PASO: GENERAMOS LAS TABLAS FINALES DE TEXTO
-- ==================================================
-- GENERAMOS LA TABLA afip.tmp_rg3685_compras PARA COMPROBANTES.
CREATE TABLE afip.tmp_rg3685_compras
	( linea character varying(1000) NOT NULL -- Linea a exportar
) WITH ( OIDS=TRUE );
INSERT INTO afip.tmp_rg3685_compras(linea)
SELECT  to_char(fecha, 'YYYYMMDD') ||
	trim(to_char(tipocomprobante, '000')) ||
	trim(to_char(nroptovta, '00000')) ||
	trim(to_char(nro, '00000000000000000000')) ||
	CASE WHEN tipocomprobante = 66 
	THEN substring(detalle,1,16)
	ELSE rpad(' ', 16, ' ') 
	END ||  -- Despacho de Importacion
	case when coddocumento = 0 then '??' else trim(to_char(coddocumento, '00')) end ||
	trim(to_char(cuit, '00000000000000000000')) ||
	rpad(nombre, 30, ' ') ||
	str_nro_to_char(impo_total, '000000000000000') ||
	str_nro_to_char(impo_nogravado, '000000000000000') ||
	str_nro_to_char(impo_exento, '000000000000000') ||
	str_nro_to_char(impo_perc_iva, '000000000000000') ||
	str_nro_to_char(impo_perc_nacionales, '000000000000000') ||
	str_nro_to_char(impo_perc_provinciales, '000000000000000') ||
	str_nro_to_char(impo_perc_municipales, '000000000000000') ||
	str_nro_to_char(impo_imp_internos, '000000000000000') ||
	'PES' || '0001000000' ||
	trim(to_char(cant_alicuotas, '0')) ||
	codigo_operacion ||
	str_nro_to_char(impo_iva, '000000000000000') ||
	'000000000000000' ||   -- Tributos no informados en el campo otros.
	trim(to_char(cuit_emisor_corredor, '00000000000')) ||
	rpad(nombre_emisor_corredor, 30, ' ') ||
	'000000000000000'  -- Iva de la comision. 
FROM afip.rg3685_compras
WHERE tipo_registro = '2-Comp' 
ORDER BY nro_linea;

-- REGENERAMOS LA TABLA afip.tmp_rg3685_alicuotas_compras PARA ALICUOTAS COMPRAS.
CREATE TABLE afip.tmp_rg3685_alicuotas_compras
( linea character varying(1000) NOT NULL -- Linea a exportar
)WITH ( OIDS=TRUE );

-- REGENERAMOS LA TABLA afip.tmp_rg3685_alicuotas_compras_desp PARA ALICUOTAS COMPRAS con despachos aduaneros.
CREATE TABLE afip.tmp_rg3685_alicuotas_compras_desp
( linea character varying(1000) NOT NULL -- Linea a exportar
)WITH ( OIDS=TRUE );

-- Se exceptuan los tipos de comprobante 66
INSERT INTO afip.tmp_rg3685_alicuotas_compras(linea)
SELECT  trim(to_char(compras.tipocomprobante, '000')) ||
	trim(to_char(compras.nroptovta, '00000')) ||
	trim(to_char(compras.nro, '00000000000000000000')) ||
	case when compras.coddocumento = 0 then '??' else trim(to_char(compras.coddocumento, '00')) end ||
	trim(to_char(compras.cuit, '00000000000000000000')) ||
	str_nro_to_char(ivas.impo_base_para_iva, '000000000000000') ||
	coalesce(trim(to_char(comiva.afipcod, '0000')), '????') ||
	str_nro_to_char(ivas.impo_iva, '000000000000000') 
FROM afip.rg3685_compras_iva ivas
	JOIN afip.rg3685_compras  compras ON ivas.idcomm = compras.idcomm and compras.tipo_registro = '2-Comp'
	LEFT JOIN comiva ON cast(comiva.tasa * 100 as integer) = cast(ivas.alicuota as integer)
WHERE compras.tipocomprobante <> 66
ORDER BY compras.nro_linea, ivas.oid;

INSERT INTO afip.tmp_rg3685_alicuotas_compras_desp(linea)
SELECT  substring(compras.detalle, 1, 16) ||
	str_nro_to_char(ivas.impo_base_para_iva, '000000000000000') ||
	coalesce(trim(to_char(comiva.afipcod, '0000')), '????') ||
	str_nro_to_char(ivas.impo_iva, '000000000000000') 
FROM afip.rg3685_compras_iva ivas
	JOIN afip.rg3685_compras  compras ON ivas.idcomm = compras.idcomm and compras.tipo_registro = '2-Comp'
	LEFT JOIN comiva ON cast(comiva.tasa * 100 as integer) = cast(ivas.alicuota as integer)
WHERE compras.tipocomprobante = 66
ORDER BY compras.nro_linea, ivas.oid;
RETURN '';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION afip.rg3685_compras() OWNER TO abacouser;


--==================================
SELECT aaVersion('446.01','446.00');
--==================================